package com.canin.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Map;
import java.util.TreeMap;

import com.canin.model.Bowl;
import com.canin.util.CaninUtil;

public class BowlDAO
{
    private Connection conn = null;

    private Map<String, String> sizes = CaninUtil.initializeSizes();

    public BowlDAO(Connection conn)
    {
        this.conn = conn;
    }

    public ArrayList<Bowl> getAllBowl() throws Exception
    {
        ArrayList<Bowl> bowlList = new ArrayList<Bowl>();

        String sql = "select * from bowl";
        PreparedStatement ps = conn.prepareStatement(sql);

        ResultSet rs = ps.executeQuery();

        while (rs.next())
        {
            Bowl bowl = new Bowl();
            bowl.setId( rs.getInt( "id" ) );
            bowl.setName( rs.getString( "name" ) );
            bowl.setBrand( rs.getString( "brand" ) );
            bowl.setDescription( rs.getString( "description" ) );
            bowl.setSize( rs.getString( "size" ) );
            bowl.setDiameter( rs.getDouble( "diameter" ) );
            bowl.setHeight( rs.getDouble( "height" ) );
            bowl.setShippingPrice( rs.getInt( "shippingPrice" ) );
            bowl.setPrice( rs.getInt( "price" ) );
            
        	bowl.setThaiSize( sizes.get( bowl.getSize() ) );
            
            bowlList.add( bowl );
        }

        rs.close();
        ps.close();

        return bowlList;
    }
	
	public TreeMap<String, String> getBowlBrand() throws Exception
	{
		TreeMap<String, String> bowlBrandList = new TreeMap<String, String>();
		
		String sql = "select distinct(brand) from bowl where brand!=''";
		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();

		while (rs.next())
		{
			bowlBrandList.put( rs.getString( "brand" ), rs.getString( "brand" ) );
		}

		rs.close();
		ps.close();
		
		return bowlBrandList;
	}
	
	public void addNewBowl( Bowl bowl ) throws Exception
	{
		String sql = "insert into bowl(name, brand, description, size, price, shippingPrice, diameter, height)" +
				" value(?,?,?,?,?,?,?,?)";
		
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, bowl.getName());
		ps.setString(2, bowl.getBrand());
		ps.setString(3, bowl.getDescription());
		ps.setString(4, bowl.getSize());
		ps.setInt(5, bowl.getPrice());
		ps.setInt(6, bowl.getShippingPrice());
		ps.setDouble(7, bowl.getDiameter());
		ps.setDouble(8, bowl.getHeight());
		
		ps.executeUpdate();
		ps.close();
	}

    public ArrayList<Bowl> getBowlBySize( String size ) throws Exception
    {
        ArrayList<Bowl> bowlList = new ArrayList<Bowl>();

        String sql = "select * from bowl where size=?";
        PreparedStatement ps = conn.prepareStatement(sql);
        ps.setString(1, size);

        ResultSet rs = ps.executeQuery();

        while (rs.next())
        {
            Bowl bowl = new Bowl();
            bowl.setId( rs.getInt( "id" ) );
            bowl.setName( rs.getString( "name" ) );
            bowl.setBrand( rs.getString( "brand" ) );
            bowl.setDescription( rs.getString( "description" ) );
            bowl.setSize( rs.getString( "size" ) );
            bowl.setDiameter( rs.getDouble( "diameter" ) );
            bowl.setHeight( rs.getDouble( "height" ) );
            bowl.setShippingPrice( rs.getInt( "shippingPrice" ) );
            bowl.setPrice( rs.getInt( "price" ) );
            bowl.setImageName( rs.getString( "imageName" ) );
            
        	bowl.setThaiSize( sizes.get( bowl.getSize() ) );
            
            bowlList.add( bowl );
        }

        rs.close();
        ps.close();

        return bowlList;
    }
    

    
}
